package com.tistory.commons.utils;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tistory.credit.vo.CreditReq;


public class XlsReadRealCard {

	private static final Logger log = LoggerFactory.getLogger(XlsReadRealCard.class);
	public static List<CreditReq> parsingExcel(String filepath,String userId,String reqDate){
		List<CreditReq>creditList = new ArrayList<CreditReq>();
		try{
			HSSFWorkbook workBook  =  new HSSFWorkbook(new FileInputStream(new File(filepath)));

			int sheetNum	= 0;
			int rowsCnt 	= 0;
			int cellsCnt 	= 0;
			HSSFSheet sheet = null;
			HSSFRow row 	= null;
			HSSFCell cell 	= null;
			String value = "";

			HashMap<String , Object> params;

			sheetNum    = workBook.getNumberOfSheets();

			log.info("\n# sheet num : " + sheetNum);

			for( int loop = 0; loop < sheetNum; loop++){			
				sheet = workBook.getSheetAt(loop);			 
				rowsCnt = sheet.getPhysicalNumberOfRows();

				for( int rownum = 1; rownum < rowsCnt; rownum++){
					CreditReq req = new CreditReq();
					row = sheet.getRow(rownum);

					log.info("\n# row num : " + rownum);

					if(row != null){
						cellsCnt = row.getPhysicalNumberOfCells();

						for(int c =0; c < cellsCnt; c++){
							params = new HashMap<String , Object>();

							value = "";
							cell = row.getCell(c);

							if(cell != null){

								switch (cell.getCellType()) {

								case XSSFCell.CELL_TYPE_FORMULA:				            	
									params.put("CELL_TYPE_FORMULA"+c, cell.getNumericCellValue());				            	
									value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
									break;

								case XSSFCell.CELL_TYPE_STRING:
									params.put("CELL_TYPE_STRING"+c, cell.getStringCellValue());				            	
									value = cell.getStringCellValue();
									break;

								case XSSFCell.CELL_TYPE_BLANK:
									params.put("CELL_TYPE_BLANK"+c, cell.getBooleanCellValue());
									break;

								case XSSFCell.CELL_TYPE_ERROR :
									params.put("CELL_TYPE_ERROR"+c, cell.getErrorCellValue());
									break;

								case XSSFCell.CELL_TYPE_NUMERIC :				            	
									params.put("CELL_TYPE_NUMERIC"+c, cell.getNumericCellValue());				            	
									value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
									break;

								case XSSFCell.CELL_TYPE_BOOLEAN :
									params.put("CELL_TYPE_BOOLEAN"+c, cell.getBooleanCellValue());
									break;

								default:
									break;
								}

								switch (c) {
								case 0:
									req.setUserId(userId);
									req.setExpectTransDate(reqDate);
									req.setCardCom(value);

									if(value.indexOf("비씨")>-1){
										req.setCardComCd("10");
									}else if (value.indexOf("국민")>-1){
										req.setCardComCd("11");
									}else if (value.indexOf("외환카드")>-1){
										req.setCardComCd("12");
									}else if (value.indexOf("삼성")>-1){
										req.setCardComCd("13");
									}else if (value.indexOf("엘지")>-1){
										req.setCardComCd("14");
									}else if (value.indexOf("현대")>-1){
										req.setCardComCd("15");
									}else if (value.indexOf("롯데")>-1){
										req.setCardComCd("16");
									}else if (value.indexOf("신한")>-1){
										req.setCardComCd("17");
									}else if (value.indexOf("전북")>-1){
										req.setCardComCd("18");
									}else if (value.indexOf("하나구")>-1){
										req.setCardComCd("19");
									}else if (value.indexOf("NH")>-1){
										req.setCardComCd("20");
									}else{
										req.setCardComCd("21");
									}
									break;
								case 4:
									req.setProdAmt(value);
									break;
								case 7:
									req.setProdFee(value);
									break;									
								case 8:
									req.setExpectProdAmt(value);
									break;

								default:
									break;
								}								
							} 

						} // for(c) 문
					}
					creditList.add(req);
				} // for(r) 문
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return creditList;
	}

}